﻿-----CSDL Quan Ly Nha Sach-----------
If Exists(Select name From sysdatabases where name = 'QuanLyNhaSach')
	Drop database QuanLyNhaSach
Go	
create database QuanLyNhaSach
Go
--Chuyen vao Database vua tao de su dung
Use QuanLyNhaSach
Go

---------------------------------Tao Bang--------------------------------------
-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'NguoiDung')
        BEGIN
                PRINT 'Xoa bang NguoiDung'
                DROP  Table NguoiDung
        END
GO
PRINT 'Tao bang NguoiDung'
CREATE TABLE NguoiDung
(
	IdNguoiDung VARCHAR(30) PRIMARY KEY,
	PassND VARCHAR(30),
	HoTen NVARCHAR(30) NOT NULL,
	NgaySinh datetime,
	GioiTinh NVARCHAR(3) NOT NULL,
	DiaChi NVARCHAR(100),
	SoDT VARCHAR(15),
	IdLoaiND int NOT NULL
)

-------------------------------1.Phieu Nhap Sach---------------------------------
If Exists(Select name From sysobjects where name = 'PhieuNhapSach')
	Drop Table PhieuNhapSach
Go	
Create Table PhieuNhapSach
(
	MaPhieuNhapSach int,
	NgayNhap datetime,
	Constraint pk_PhieuNhapSach Primary key(MaPhieuNhapSach)
)

-----------------------------2.Tac Gia---------------------------------

If Exists(Select name From sysobjects where name = 'TacGia')
	Drop Table TacGia
Go	
Create Table TacGia
(
	MaTacGia int,
	TenTacGia nvarchar(100),
	Constraint pk_TacGia Primary key(MaTacGia)
)

-----------------------------3.Loai Sach---------------------------------

If Exists(Select name From sysobjects where name = 'LoaiSach')
	Drop Table LoaiSach
Go	
Create Table LoaiSach
(
	MaLoaiSach int,
	TenTheLoai nvarchar(100),
	Constraint pk_LoaiSach Primary key(MaLoaiSach)
)

-----------------------------4.Sach---------------------------------
If Exists(Select name From sysobjects where name = 'Sach')
	Drop Table Sach
Go	
Create Table Sach
(
	MaSach int,
	TenSach nvarchar(100),
	MaLoaiSach int,
	MaTacGia int,
	SoLuongTon int,
	DonGia float,
	Constraint pk_Sach Primary key(MaSach)
)

-----------------------------5.Chi Tiet Phieu Nhap Sach---------------------------------
If Exists(Select name From sysobjects where name = 'ChiTietPhieuNhapSach')
	Drop Table ChiTietPhieuNhapSach
Go	
Create Table ChiTietPhieuNhapSach
(
	MaChiTietPhieuNhapSach int,
	MaPhieuNhapSach int,
	SoLuongNhap int,
	MaSach int,
	Constraint pk_ChiTietPhieuNhapSach Primary key(MaChiTietPhieuNhapSach)
)

-----------------------------6.Hoa Don---------------------------------
If Exists(Select name From sysobjects where name = 'HoaDon')
	Drop Table HoaDon
Go	
Create Table HoaDon
(
	MaHoaDon int,
	NgayLapHoaDon datetime,
	MaKhachHang int,	
	Constraint pk_HoaDon Primary key(MaHoaDon)
)

-----------------------------7.Khach Hang---------------------------------
If Exists(Select name From sysobjects where name = 'KhachHang')
	Drop Table KhachHang
Go	
Create Table KhachHang
(
	MaKhachHang int,
	TenKhachHang nvarchar(50),
	NoKhachHang float,
	Constraint pk_KhachHang Primary key(MaKhachHang)
)

-----------------------------8.Chi Tiet Hoa Don---------------------------------
If Exists(Select name From sysobjects where name = 'ChiTietHoaDon')
	Drop Table ChiTietHoaDon
Go	
Create Table ChiTietHoaDon
(
	MaChiTietHoaDon int,
	MaHoaDon int,
	MaSach int,
	SoLuongXuat int,
	DonGia float,
	Constraint pk_ChiTietHoaDon Primary key(MaChiTietHoaDon)
)
-----------------------------9.Tham So---------------------------------
If Exists(Select name From sysobjects where name = 'ThamSo')
	Drop Table ThamSo
Go	
Create Table ThamSo
(
	MaThamSo int,
	SoLuongNhapToiThieu int,
	SoLuongTonToiThieu int,
	NoToiDa int,
	SoLuongTonItNhat int,
	Constraint pk_ThamSo Primary key(MaThamSo)
)

Go
----------------------------------------Tao Khoa Ngoai--------------------------------
--------------------------------------------------------------------------------------
Alter Table Sach
Add Constraint fk_Sach_TacGia Foreign key(MaTacGia)
								References TacGia(MaTacGia)		
Go

Alter Table Sach
Add Constraint fk_Sach_LoaiSach Foreign key(MaLoaiSach)
								References LoaiSach(MaLoaiSach)		
Go

Alter Table ChiTietPhieuNhapSach
Add Constraint fk_ChiTietPhieuNhapSach_PhieuNhapSach Foreign key(MaPhieuNhapSach)
								References PhieuNhapSach(MaPhieuNhapSach)		
Go

Alter Table ChiTietPhieuNhapSach
Add Constraint fk_ChiTietPhieuNhapSach_Sach Foreign key(MaSach)
								References Sach(MaSach)		
Go

Alter Table ChiTietHoaDon
Add Constraint fk_ChiTietHoaDon_HoaDon Foreign key(MaHoaDon)
								References HoaDon(MaHoaDon)		
Go

Alter Table ChiTietHoaDon
Add Constraint fk_ChiTietHoaDon_Sach Foreign key(MaSach)
								References Sach(MaSach)		
Go

Alter Table HoaDon
Add Constraint fk_HoaDon_KhachHang Foreign key(MaKhachHang)
								References KhachHang(MaKhachHang)		
Go


-----------------------------------Them Du Lieu Cho Cac Bang---------------------------------
-----------------------------------Tabl.NguoiDung--------------------------------------------
---------------------------------------------------------------------------------------------
INSERT INTO NguoiDung(IdNguoiDung, PassND, HoTen, NgaySinh, GioiTinh, DiaChi, SoDT, IdLoaiND) 
VALUES('12345','12345',N'Bùi Thanh Thoại','1990/06/26',N'Nam',N'1118, Tỉnh lộ 10, P.Tân Tạo, Q.Bình Tân','0922571760','1')

INSERT INTO NguoiDung(IdNguoiDung, PassND, HoTen, NgaySinh, GioiTinh, DiaChi, SoDT, IdLoaiND) 
VALUES('54321','54321',N'Trần Văn Tú','1990/06/26',N'Nam','','','2')